{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# cpu2017数据源 贴源数据层 代码实现\n",
    "\n",
    "## 1. 下载最新的数据源"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests, os\n",
    "\n",
    "def download_latest_results(benchmark_name: str):\n",
    "    \"\"\"\n",
    "    下载最新的数据源\n",
    "    :param benchmark_name: 数据源名称，应当是cpu2017, cpu2006, jbb2015, jvm2008, power_ssj2008其中的一个\n",
    "    :return: 返回成功下载的文件路径名\n",
    "    \"\"\"\n",
    "    benchmark_list = [\"cpu2017\", \"cpu2006\", \"jbb2015\", \"jvm2008\", \"power_ssj2008\"]\n",
    "    assert benchmark_name in benchmark_list\n",
    "    # 根据数据源名称生成下载路径\n",
    "    download_addr = f\"https://www.spec.org/cgi-bin/osgresults?conf={benchmark_name};op=dump;format=csvdump\"\n",
    "    try:\n",
    "        response = requests.get(download_addr, stream=True)\n",
    "        response.raise_for_status()\n",
    "        # 从response的header中获得文件名，这个文件名包含了数据源名称以及下载时的日期时间\n",
    "        file_name = response.headers[\"Content-Disposition\"].split(\";\")[1].split(\"=\")[1][1:-1]\n",
    "        file_path = os.path.join(\"data\", file_name)\n",
    "        # 写入文件\n",
    "        with open(file_path, \"wb\") as f:\n",
    "            for chunk in response.iter_content(chunk_size=1048576):\n",
    "                if chunk:\n",
    "                    f.write(chunk)\n",
    "        return file_path\n",
    "    except Exception as e:\n",
    "        print(\"failed to download results\")\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "download_latest_results(\"cpu2017\")"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n",
     "is_executing": true
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 2. 获得已经下载到本地的最新数据源"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "outputs": [],
   "source": [
    "def get_latest_results(benchmark_name: str, delete_old: bool = False):\n",
    "    \"\"\"\n",
    "    返回本地最新的数据源文件路径名\n",
    "    :param benchmark_name: 数据源名称，应当是cpu2017, cpu2006, jbb2015, jvm2008, power_ssj2008其中的一个\n",
    "    :param delete_old: 是否删除旧的数据源文件\n",
    "    :return: 返回最新的数据源文件路径名\n",
    "    \"\"\"\n",
    "    benchmark_list = [\"cpu2017\", \"cpu2006\", \"jbb2015\", \"jvm2008\", \"power_ssj2008\"]\n",
    "    assert benchmark_name in benchmark_list\n",
    "    all_file_path_list = os.listdir(\"data/raw\")  # 获取data文件夹下所有文件名的列表\n",
    "    benchmark_file_path_list = [i for i in all_file_path_list if i.find(benchmark_name) != -1]  # 找到属于该数据源的所有文件\n",
    "    max_time = 0  # 记录数据源文件的下载时间\n",
    "    max_time_index = 0  # 记录数据源文件的在列表中的下标\n",
    "    for index, i in enumerate(benchmark_file_path_list):\n",
    "        time = int(i.split(\"-\")[-2] + i.split(\"-\")[-1].split(\".\")[0])\n",
    "        if time > max_time:  # 保留数值最大（即下载时间最新）的数据源文件下标\n",
    "            max_time = time\n",
    "            max_time_index = index\n",
    "    latest_file_path = benchmark_file_path_list[max_time_index]  # 最新的数据源文件名称\n",
    "    if delete_old:  # 如果需要删除旧的数据源文件\n",
    "        del benchmark_file_path_list[max_time_index]\n",
    "        for i in benchmark_file_path_list:\n",
    "            os.remove(os.path.join(\"data/raw\", i))\n",
    "    return os.path.join(\"data/raw\", latest_file_path)  # 生成路径名并返回\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "get_latest_results(benchmark_name=\"cpu2017\", delete_old=True)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 3. 使用pandas读取CSV文件并整理成DataFrame"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "outputs": [
    {
     "data": {
      "text/plain": "         Benchmark      Hardware Vendor\\t  \\\n0         CINT2017  ASUSTeK Computer Inc.   \n1         CINT2017  ASUSTeK Computer Inc.   \n2         CINT2017  ASUSTeK Computer Inc.   \n3         CINT2017  ASUSTeK Computer Inc.   \n4         CINT2017  ASUSTeK Computer Inc.   \n...            ...                    ...   \n27473  CFP2017rate                xFusion   \n27474  CFP2017rate                xFusion   \n27475  CFP2017rate                xFusion   \n27476  CFP2017rate                xFusion   \n27477  CFP2017rate                xFusion   \n\n                                                  System  Peak Result  \\\n0      ASUS ESC4000A-E10(KRPG-U8) Server System 2.60 ...         9.09   \n1      ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...         5.15   \n2      ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...         5.22   \n3      ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...        10.30   \n4      ASUS ESC8000 G4(Z11PG-D24) Server System (2.10...        12.00   \n...                                                  ...          ...   \n27473           xFusion 5288 V6 (Intel Xeon Silver 4310)         0.00   \n27474           xFusion 5288 V6 (Intel Xeon Silver 4314)         0.00   \n27475           xFusion 5288 V6 (Intel Xeon Silver 4316)         0.00   \n27476  xFusion KunLun 9008 V5 (Intel Xeon Platinum 8280)         0.00   \n27477  xFusion KunLun 9008 V5 (Intel Xeon Platinum 8280)      1160.00   \n\n       Base Result  Energy Peak Result  Energy Base Result  # Cores  # Chips   \\\n0             8.87                 0.0                 0.0       64         1   \n1             5.05                 0.0                 0.0       12         2   \n2             5.15                 0.0                 0.0       16         2   \n3            10.00                 0.0                 0.0       44         2   \n4            11.70                 0.0                 0.0       40         2   \n...            ...                 ...                 ...      ...       ...   \n27473       203.00                 0.0                 0.0       24         2   \n27474       264.00                 0.0                 0.0       32         2   \n27475       300.00                 0.0                 0.0       40         2   \n27476      1150.00                 0.0                 0.0      224         8   \n27477      1140.00                 0.0                 0.0      224         8   \n\n       # Enabled Threads Per Core  ...  HW Avail  SW Avail License  \\\n0                               2  ...  Jul-2020  Jun-2019    9016   \n1                               1  ...  Apr-2019  May-2019    9016   \n2                               1  ...  Feb-2020  Jun-2019    9016   \n3                               1  ...  Apr-2019  Nov-2018    9016   \n4                               1  ...  Feb-2020  Apr-2020    9016   \n...                           ...  ...       ...       ...     ...   \n27473                           2  ...  Apr-2021  May-2021    6488   \n27474                           2  ...  Apr-2021  May-2021    6488   \n27475                           2  ...  Apr-2021  May-2021    6488   \n27476                           2  ...  Apr-2019  Dec-2020    6488   \n27477                           2  ...  Apr-2019  Dec-2020    6488   \n\n                   Tested By           Test Sponsor Test Date Published  \\\n0      ASUSTeK Computer Inc.  ASUSTeK Computer Inc.  Jun-2020  Jul-2020   \n1      ASUSTeK Computer Inc.  ASUSTeK Computer Inc.  Aug-2019  Sep-2019   \n2      ASUSTeK Computer Inc.  ASUSTeK Computer Inc.  May-2020  Jul-2020   \n3      ASUSTeK Computer Inc.  ASUSTeK Computer Inc.  May-2019  Jun-2019   \n4      ASUSTeK Computer Inc.  ASUSTeK Computer Inc.  Jun-2020  Aug-2020   \n...                      ...                    ...       ...       ...   \n27473                xFusion                xFusion  Mar-2022  Apr-2022   \n27474                xFusion                xFusion  Mar-2022  Apr-2022   \n27475                xFusion                xFusion  Mar-2022  Apr-2022   \n27476                xFusion                xFusion  Feb-2022  Mar-2022   \n27477                xFusion                xFusion  Feb-2022  Mar-2022   \n\n       Updated                                          Disclosure Disclosures  \n0      Jul-2020  <A HREF=\"/cpu2017/results/res2020q3/cpu2017-20...         NaN  \n1      Dec-2020  <A HREF=\"/cpu2017/results/res2019q3/cpu2017-20...         NaN  \n2      Jul-2020  <A HREF=\"/cpu2017/results/res2020q3/cpu2017-20...         NaN  \n3      Dec-2020  <A HREF=\"/cpu2017/results/res2019q2/cpu2017-20...         NaN  \n4      Aug-2020  <A HREF=\"/cpu2017/results/res2020q3/cpu2017-20...         NaN  \n...         ...                                                ...         ...  \n27473  Apr-2022  <A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...         NaN  \n27474  Apr-2022  <A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...         NaN  \n27475  Apr-2022  <A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...         NaN  \n27476  Mar-2022  <A HREF=\"/cpu2017/results/res2022q1/cpu2017-20...         NaN  \n27477  Mar-2022  <A HREF=\"/cpu2017/results/res2022q1/cpu2017-20...         NaN  \n\n[27478 rows x 35 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Benchmark</th>\n      <th>Hardware Vendor\\t</th>\n      <th>System</th>\n      <th>Peak Result</th>\n      <th>Base Result</th>\n      <th>Energy Peak Result</th>\n      <th>Energy Base Result</th>\n      <th># Cores</th>\n      <th># Chips</th>\n      <th># Enabled Threads Per Core</th>\n      <th>...</th>\n      <th>HW Avail</th>\n      <th>SW Avail</th>\n      <th>License</th>\n      <th>Tested By</th>\n      <th>Test Sponsor</th>\n      <th>Test Date</th>\n      <th>Published</th>\n      <th>Updated</th>\n      <th>Disclosure</th>\n      <th>Disclosures</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>CINT2017</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUS ESC4000A-E10(KRPG-U8) Server System 2.60 ...</td>\n      <td>9.09</td>\n      <td>8.87</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>64</td>\n      <td>1</td>\n      <td>2</td>\n      <td>...</td>\n      <td>Jul-2020</td>\n      <td>Jun-2019</td>\n      <td>9016</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>Jun-2020</td>\n      <td>Jul-2020</td>\n      <td>Jul-2020</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2020q3/cpu2017-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>CINT2017</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...</td>\n      <td>5.15</td>\n      <td>5.05</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>12</td>\n      <td>2</td>\n      <td>1</td>\n      <td>...</td>\n      <td>Apr-2019</td>\n      <td>May-2019</td>\n      <td>9016</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>Aug-2019</td>\n      <td>Sep-2019</td>\n      <td>Dec-2020</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2019q3/cpu2017-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>CINT2017</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...</td>\n      <td>5.22</td>\n      <td>5.15</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>16</td>\n      <td>2</td>\n      <td>1</td>\n      <td>...</td>\n      <td>Feb-2020</td>\n      <td>Jun-2019</td>\n      <td>9016</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>May-2020</td>\n      <td>Jul-2020</td>\n      <td>Jul-2020</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2020q3/cpu2017-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>CINT2017</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...</td>\n      <td>10.30</td>\n      <td>10.00</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>44</td>\n      <td>2</td>\n      <td>1</td>\n      <td>...</td>\n      <td>Apr-2019</td>\n      <td>Nov-2018</td>\n      <td>9016</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>May-2019</td>\n      <td>Jun-2019</td>\n      <td>Dec-2020</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2019q2/cpu2017-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>CINT2017</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUS ESC8000 G4(Z11PG-D24) Server System (2.10...</td>\n      <td>12.00</td>\n      <td>11.70</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>40</td>\n      <td>2</td>\n      <td>1</td>\n      <td>...</td>\n      <td>Feb-2020</td>\n      <td>Apr-2020</td>\n      <td>9016</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>Jun-2020</td>\n      <td>Aug-2020</td>\n      <td>Aug-2020</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2020q3/cpu2017-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>27473</th>\n      <td>CFP2017rate</td>\n      <td>xFusion</td>\n      <td>xFusion 5288 V6 (Intel Xeon Silver 4310)</td>\n      <td>0.00</td>\n      <td>203.00</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>24</td>\n      <td>2</td>\n      <td>2</td>\n      <td>...</td>\n      <td>Apr-2021</td>\n      <td>May-2021</td>\n      <td>6488</td>\n      <td>xFusion</td>\n      <td>xFusion</td>\n      <td>Mar-2022</td>\n      <td>Apr-2022</td>\n      <td>Apr-2022</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>27474</th>\n      <td>CFP2017rate</td>\n      <td>xFusion</td>\n      <td>xFusion 5288 V6 (Intel Xeon Silver 4314)</td>\n      <td>0.00</td>\n      <td>264.00</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>32</td>\n      <td>2</td>\n      <td>2</td>\n      <td>...</td>\n      <td>Apr-2021</td>\n      <td>May-2021</td>\n      <td>6488</td>\n      <td>xFusion</td>\n      <td>xFusion</td>\n      <td>Mar-2022</td>\n      <td>Apr-2022</td>\n      <td>Apr-2022</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>27475</th>\n      <td>CFP2017rate</td>\n      <td>xFusion</td>\n      <td>xFusion 5288 V6 (Intel Xeon Silver 4316)</td>\n      <td>0.00</td>\n      <td>300.00</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>40</td>\n      <td>2</td>\n      <td>2</td>\n      <td>...</td>\n      <td>Apr-2021</td>\n      <td>May-2021</td>\n      <td>6488</td>\n      <td>xFusion</td>\n      <td>xFusion</td>\n      <td>Mar-2022</td>\n      <td>Apr-2022</td>\n      <td>Apr-2022</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>27476</th>\n      <td>CFP2017rate</td>\n      <td>xFusion</td>\n      <td>xFusion KunLun 9008 V5 (Intel Xeon Platinum 8280)</td>\n      <td>0.00</td>\n      <td>1150.00</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>224</td>\n      <td>8</td>\n      <td>2</td>\n      <td>...</td>\n      <td>Apr-2019</td>\n      <td>Dec-2020</td>\n      <td>6488</td>\n      <td>xFusion</td>\n      <td>xFusion</td>\n      <td>Feb-2022</td>\n      <td>Mar-2022</td>\n      <td>Mar-2022</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2022q1/cpu2017-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>27477</th>\n      <td>CFP2017rate</td>\n      <td>xFusion</td>\n      <td>xFusion KunLun 9008 V5 (Intel Xeon Platinum 8280)</td>\n      <td>1160.00</td>\n      <td>1140.00</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>224</td>\n      <td>8</td>\n      <td>2</td>\n      <td>...</td>\n      <td>Apr-2019</td>\n      <td>Dec-2020</td>\n      <td>6488</td>\n      <td>xFusion</td>\n      <td>xFusion</td>\n      <td>Feb-2022</td>\n      <td>Mar-2022</td>\n      <td>Mar-2022</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2022q1/cpu2017-20...</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n<p>27478 rows × 35 columns</p>\n</div>"
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "cpu2017_data = pd.read_csv(get_latest_results(benchmark_name=\"cpu2017\"))\n",
    "cpu2017_data"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "需要根据benchmark列的名称进行筛选，有4种Benchmark名称，分别对应4张ods表格"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "outputs": [
    {
     "data": {
      "text/plain": "     Benchmark      Hardware Vendor\\t  \\\n0     CINT2017  ASUSTeK Computer Inc.   \n1     CINT2017  ASUSTeK Computer Inc.   \n2     CINT2017  ASUSTeK Computer Inc.   \n3     CINT2017  ASUSTeK Computer Inc.   \n4     CINT2017  ASUSTeK Computer Inc.   \n...        ...                    ...   \n6073  CINT2017                xFusion   \n6074  CINT2017                xFusion   \n6075  CINT2017                xFusion   \n6076  CINT2017                xFusion   \n6077  CINT2017                xFusion   \n\n                                                 System  Peak Result  \\\n0     ASUS ESC4000A-E10(KRPG-U8) Server System 2.60 ...         9.09   \n1     ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...         5.15   \n2     ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...         5.22   \n3     ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...        10.30   \n4     ASUS ESC8000 G4(Z11PG-D24) Server System (2.10...        12.00   \n...                                                 ...          ...   \n6073            xFusion 5288 V6 (Intel Xeon Gold 6336Y)         0.00   \n6074             xFusion 5288 V6 (Intel Xeon Gold 6342)         0.00   \n6075           xFusion 5288 V6 (Intel Xeon Silver 4310)         0.00   \n6076           xFusion 5288 V6 (Intel Xeon Silver 4314)         0.00   \n6077           xFusion 5288 V6 (Intel Xeon Silver 4316)         0.00   \n\n      Base Result  Energy Peak Result  Energy Base Result  # Cores  # Chips   \\\n0            8.87                 0.0                 0.0       64         1   \n1            5.05                 0.0                 0.0       12         2   \n2            5.15                 0.0                 0.0       16         2   \n3           10.00                 0.0                 0.0       44         2   \n4           11.70                 0.0                 0.0       40         2   \n...           ...                 ...                 ...      ...       ...   \n6073        12.30                 0.0                 0.0       48         2   \n6074        12.00                 0.0                 0.0       48         2   \n6075        10.80                 0.0                 0.0       24         2   \n6076        11.40                 0.0                 0.0       32         2   \n6077        11.60                 0.0                 0.0       40         2   \n\n      # Enabled Threads Per Core  ...  HW Avail  SW Avail License  \\\n0                              2  ...  Jul-2020  Jun-2019    9016   \n1                              1  ...  Apr-2019  May-2019    9016   \n2                              1  ...  Feb-2020  Jun-2019    9016   \n3                              1  ...  Apr-2019  Nov-2018    9016   \n4                              1  ...  Feb-2020  Apr-2020    9016   \n...                          ...  ...       ...       ...     ...   \n6073                           1  ...  Apr-2021  May-2021    6488   \n6074                           1  ...  Apr-2021  May-2021    6488   \n6075                           1  ...  Apr-2021  May-2021    6488   \n6076                           1  ...  Apr-2021  May-2021    6488   \n6077                           1  ...  Apr-2021  May-2021    6488   \n\n                  Tested By           Test Sponsor Test Date Published  \\\n0     ASUSTeK Computer Inc.  ASUSTeK Computer Inc.  Jun-2020  Jul-2020   \n1     ASUSTeK Computer Inc.  ASUSTeK Computer Inc.  Aug-2019  Sep-2019   \n2     ASUSTeK Computer Inc.  ASUSTeK Computer Inc.  May-2020  Jul-2020   \n3     ASUSTeK Computer Inc.  ASUSTeK Computer Inc.  May-2019  Jun-2019   \n4     ASUSTeK Computer Inc.  ASUSTeK Computer Inc.  Jun-2020  Aug-2020   \n...                     ...                    ...       ...       ...   \n6073                xFusion                xFusion  Mar-2022  Apr-2022   \n6074                xFusion                xFusion  Mar-2022  Apr-2022   \n6075                xFusion                xFusion  Mar-2022  Apr-2022   \n6076                xFusion                xFusion  Mar-2022  Apr-2022   \n6077                xFusion                xFusion  Mar-2022  Apr-2022   \n\n      Updated                                          Disclosure Disclosures  \n0     Jul-2020  <A HREF=\"/cpu2017/results/res2020q3/cpu2017-20...         NaN  \n1     Dec-2020  <A HREF=\"/cpu2017/results/res2019q3/cpu2017-20...         NaN  \n2     Jul-2020  <A HREF=\"/cpu2017/results/res2020q3/cpu2017-20...         NaN  \n3     Dec-2020  <A HREF=\"/cpu2017/results/res2019q2/cpu2017-20...         NaN  \n4     Aug-2020  <A HREF=\"/cpu2017/results/res2020q3/cpu2017-20...         NaN  \n...        ...                                                ...         ...  \n6073  Apr-2022  <A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...         NaN  \n6074  Apr-2022  <A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...         NaN  \n6075  Apr-2022  <A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...         NaN  \n6076  Apr-2022  <A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...         NaN  \n6077  Apr-2022  <A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...         NaN  \n\n[6078 rows x 35 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Benchmark</th>\n      <th>Hardware Vendor\\t</th>\n      <th>System</th>\n      <th>Peak Result</th>\n      <th>Base Result</th>\n      <th>Energy Peak Result</th>\n      <th>Energy Base Result</th>\n      <th># Cores</th>\n      <th># Chips</th>\n      <th># Enabled Threads Per Core</th>\n      <th>...</th>\n      <th>HW Avail</th>\n      <th>SW Avail</th>\n      <th>License</th>\n      <th>Tested By</th>\n      <th>Test Sponsor</th>\n      <th>Test Date</th>\n      <th>Published</th>\n      <th>Updated</th>\n      <th>Disclosure</th>\n      <th>Disclosures</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>CINT2017</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUS ESC4000A-E10(KRPG-U8) Server System 2.60 ...</td>\n      <td>9.09</td>\n      <td>8.87</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>64</td>\n      <td>1</td>\n      <td>2</td>\n      <td>...</td>\n      <td>Jul-2020</td>\n      <td>Jun-2019</td>\n      <td>9016</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>Jun-2020</td>\n      <td>Jul-2020</td>\n      <td>Jul-2020</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2020q3/cpu2017-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>CINT2017</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...</td>\n      <td>5.15</td>\n      <td>5.05</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>12</td>\n      <td>2</td>\n      <td>1</td>\n      <td>...</td>\n      <td>Apr-2019</td>\n      <td>May-2019</td>\n      <td>9016</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>Aug-2019</td>\n      <td>Sep-2019</td>\n      <td>Dec-2020</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2019q3/cpu2017-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>CINT2017</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...</td>\n      <td>5.22</td>\n      <td>5.15</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>16</td>\n      <td>2</td>\n      <td>1</td>\n      <td>...</td>\n      <td>Feb-2020</td>\n      <td>Jun-2019</td>\n      <td>9016</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>May-2020</td>\n      <td>Jul-2020</td>\n      <td>Jul-2020</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2020q3/cpu2017-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>CINT2017</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...</td>\n      <td>10.30</td>\n      <td>10.00</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>44</td>\n      <td>2</td>\n      <td>1</td>\n      <td>...</td>\n      <td>Apr-2019</td>\n      <td>Nov-2018</td>\n      <td>9016</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>May-2019</td>\n      <td>Jun-2019</td>\n      <td>Dec-2020</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2019q2/cpu2017-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>CINT2017</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUS ESC8000 G4(Z11PG-D24) Server System (2.10...</td>\n      <td>12.00</td>\n      <td>11.70</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>40</td>\n      <td>2</td>\n      <td>1</td>\n      <td>...</td>\n      <td>Feb-2020</td>\n      <td>Apr-2020</td>\n      <td>9016</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>Jun-2020</td>\n      <td>Aug-2020</td>\n      <td>Aug-2020</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2020q3/cpu2017-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>6073</th>\n      <td>CINT2017</td>\n      <td>xFusion</td>\n      <td>xFusion 5288 V6 (Intel Xeon Gold 6336Y)</td>\n      <td>0.00</td>\n      <td>12.30</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>48</td>\n      <td>2</td>\n      <td>1</td>\n      <td>...</td>\n      <td>Apr-2021</td>\n      <td>May-2021</td>\n      <td>6488</td>\n      <td>xFusion</td>\n      <td>xFusion</td>\n      <td>Mar-2022</td>\n      <td>Apr-2022</td>\n      <td>Apr-2022</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>6074</th>\n      <td>CINT2017</td>\n      <td>xFusion</td>\n      <td>xFusion 5288 V6 (Intel Xeon Gold 6342)</td>\n      <td>0.00</td>\n      <td>12.00</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>48</td>\n      <td>2</td>\n      <td>1</td>\n      <td>...</td>\n      <td>Apr-2021</td>\n      <td>May-2021</td>\n      <td>6488</td>\n      <td>xFusion</td>\n      <td>xFusion</td>\n      <td>Mar-2022</td>\n      <td>Apr-2022</td>\n      <td>Apr-2022</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>6075</th>\n      <td>CINT2017</td>\n      <td>xFusion</td>\n      <td>xFusion 5288 V6 (Intel Xeon Silver 4310)</td>\n      <td>0.00</td>\n      <td>10.80</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>24</td>\n      <td>2</td>\n      <td>1</td>\n      <td>...</td>\n      <td>Apr-2021</td>\n      <td>May-2021</td>\n      <td>6488</td>\n      <td>xFusion</td>\n      <td>xFusion</td>\n      <td>Mar-2022</td>\n      <td>Apr-2022</td>\n      <td>Apr-2022</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>6076</th>\n      <td>CINT2017</td>\n      <td>xFusion</td>\n      <td>xFusion 5288 V6 (Intel Xeon Silver 4314)</td>\n      <td>0.00</td>\n      <td>11.40</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>32</td>\n      <td>2</td>\n      <td>1</td>\n      <td>...</td>\n      <td>Apr-2021</td>\n      <td>May-2021</td>\n      <td>6488</td>\n      <td>xFusion</td>\n      <td>xFusion</td>\n      <td>Mar-2022</td>\n      <td>Apr-2022</td>\n      <td>Apr-2022</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>6077</th>\n      <td>CINT2017</td>\n      <td>xFusion</td>\n      <td>xFusion 5288 V6 (Intel Xeon Silver 4316)</td>\n      <td>0.00</td>\n      <td>11.60</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>40</td>\n      <td>2</td>\n      <td>1</td>\n      <td>...</td>\n      <td>Apr-2021</td>\n      <td>May-2021</td>\n      <td>6488</td>\n      <td>xFusion</td>\n      <td>xFusion</td>\n      <td>Mar-2022</td>\n      <td>Apr-2022</td>\n      <td>Apr-2022</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n<p>6078 rows × 35 columns</p>\n</div>"
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cpu2017_data.query(\"Benchmark == 'CINT2017'\")"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 4. 贴源数据层的实现：从数据源中抽取相关的属性并聚合\n",
    "\n",
    "尝试对数据进行一些处理，例如对同一个生产厂家的机器做一个聚合，从生产厂家的角度考察机器的Java性能"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 4.1 抽取感兴趣的属性\n",
    "\n",
    "感兴趣的属性：\n",
    "- Hardware Vendor机器生产公司\n",
    "- System系统型号\n",
    "- Base Result得分\n",
    "- \\# Cores核心数\n",
    "- \\# Chips\n",
    "- Processor处理器型号【是统一的数据主体，合并数据时以该属性作为准则】\n",
    "- Processor MHz CPU频率\n",
    "- 各级Cache容量：1st Level Cache, 2nd Level Cache, 3rd Level Cache, Other Cache\n",
    "- Memory存储大小\n",
    "- Updated最后分数记录时间\n",
    "- Disclosure详细结果报告的链接\n",
    "\n",
    "#### 主要处理的内容为\n",
    "将cpu2017的属性名向jvm2008靠拢，更新提取的属性名。\n",
    "\\# 更新部分属性名 [\"Company\", \"System\", \"Result\", \"# cores\", \"Processor\", \"CPU Speed(MHz)\", \"1st Cache per core(KB)\",\n",
    "\\# \"2nd Cache per core(KB)\", \"3rd Cache per chip(MB)\", \"Other Cache per chip(MB)\", \"Memory(GB)\", \"Updated\",\n",
    "\\# \"Report Link\"]"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "outputs": [
    {
     "data": {
      "text/plain": "                     Company  \\\n0      ASUSTeK Computer Inc.   \n1      ASUSTeK Computer Inc.   \n2      ASUSTeK Computer Inc.   \n3      ASUSTeK Computer Inc.   \n4      ASUSTeK Computer Inc.   \n...                      ...   \n27473                xFusion   \n27474                xFusion   \n27475                xFusion   \n27476                xFusion   \n27477                xFusion   \n\n                                                  System   Result  # cores  \\\n0      ASUS ESC4000A-E10(KRPG-U8) Server System 2.60 ...     8.87       64   \n1      ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...     5.05       12   \n2      ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...     5.15       16   \n3      ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...    10.00       44   \n4      ASUS ESC8000 G4(Z11PG-D24) Server System (2.10...    11.70       40   \n...                                                  ...      ...      ...   \n27473           xFusion 5288 V6 (Intel Xeon Silver 4310)   203.00       24   \n27474           xFusion 5288 V6 (Intel Xeon Silver 4314)   264.00       32   \n27475           xFusion 5288 V6 (Intel Xeon Silver 4316)   300.00       40   \n27476  xFusion KunLun 9008 V5 (Intel Xeon Platinum 8280)  1150.00      224   \n27477  xFusion KunLun 9008 V5 (Intel Xeon Platinum 8280)  1140.00      224   \n\n       # chips                 Processor  CPU Speed(MHz)  \\\n0            1             AMD EPYC 7H12            2600   \n1            2    Intel Xeon Bronze 3204            1900   \n2            2   Intel Xeon Bronze 3206R            1900   \n3            2     Intel Xeon Gold 6238T            1900   \n4            2     Intel Xeon Gold 5218R            2100   \n...        ...                       ...             ...   \n27473        2    Intel Xeon Silver 4310            2100   \n27474        2    Intel Xeon Silver 4314            2400   \n27475        2    Intel Xeon Silver 4316            2300   \n27476        8  Intel Xeon Platinum 8280            2700   \n27477        8  Intel Xeon Platinum 8280            2700   \n\n                   1st Cache per core(KB)        2nd Cache per core(KB)  \\\n0      32 KB I + 32 KB D on chip per core   512 KB I+D on chip per core   \n1      32 KB I + 32 KB D on chip per core     1 MB I+D on chip per core   \n2      32 KB I + 32 KB D on chip per core     1 MB I+D on chip per core   \n3      32 KB I + 32 KB D on chip per core     1 MB I+D on chip per core   \n4      32 KB I + 32 KB D on chip per core     1 MB I+D on chip per core   \n...                                   ...                           ...   \n27473  32 KB I + 48 KB D on chip per core  1.25 MB I+D on chip per core   \n27474  32 KB I + 48 KB D on chip per core  1.25 MB I+D on chip per core   \n27475  32 KB I + 48 KB D on chip per core  1.25 MB I+D on chip per core   \n27476  32 KB I + 32 KB D on chip per core     1 MB I+D on chip per core   \n27477  32 KB I + 32 KB D on chip per core     1 MB I+D on chip per core   \n\n                                  3rd Cache per chip(MB)  \\\n0      256 MB I+D on chip per chip, 16 MB shared / 4 ...   \n1                           8.25 MB I+D on chip per chip   \n2                             11 MB I+D on chip per chip   \n3                          30.25 MB I+D on chip per chip   \n4                           27.5 MB I+D on chip per chip   \n...                                                  ...   \n27473                         18 MB I+D on chip per chip   \n27474                         24 MB I+D on chip per chip   \n27475                         30 MB I+D on chip per chip   \n27476                       38.5 MB I+D on chip per chip   \n27477                       38.5 MB I+D on chip per chip   \n\n      Other Cache per chip(MB)  \\\n0                         None   \n1                         None   \n2                         None   \n3                         None   \n4                         None   \n...                        ...   \n27473                     None   \n27474                     None   \n27475                     None   \n27476                     None   \n27477                     None   \n\n                                              Memory(GB)   Updated  \\\n0                   512 GB (8 x 64 GB 2Rx4 PC4-3200AA-R)  Jul-2020   \n1      768 GB (24 x 32 GB 2Rx4 PC4-2933Y-R, running a...  Dec-2020   \n2      768 GB (24 x 32 GB 2Rx4 PC4-2933Y-R, running a...  Jul-2020   \n3                   768 GB (24 x 32 GB 2Rx4 PC4-2933Y-R)  Dec-2020   \n4      768 GB (24 x 32 GB 2Rx4 PC4-2933Y-R, running a...  Aug-2020   \n...                                                  ...       ...   \n27473  512 GB (16 x 32 GB 2Rx4 PC4-3200AA-R, running ...  Apr-2022   \n27474  512 GB (16 x 32 GB 2Rx4 PC4-3200AA-R, running ...  Apr-2022   \n27475  512 GB (16 x 32 GB 2Rx4 PC4-3200AA-R, running ...  Apr-2022   \n27476                 3 TB (96 x 32 GB 2Rx4 PC4-2933Y-R)  Mar-2022   \n27477                 3 TB (96 x 32 GB 2Rx4 PC4-2933Y-R)  Mar-2022   \n\n                                             Report Link  \n0      <A HREF=\"/cpu2017/results/res2020q3/cpu2017-20...  \n1      <A HREF=\"/cpu2017/results/res2019q3/cpu2017-20...  \n2      <A HREF=\"/cpu2017/results/res2020q3/cpu2017-20...  \n3      <A HREF=\"/cpu2017/results/res2019q2/cpu2017-20...  \n4      <A HREF=\"/cpu2017/results/res2020q3/cpu2017-20...  \n...                                                  ...  \n27473  <A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...  \n27474  <A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...  \n27475  <A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...  \n27476  <A HREF=\"/cpu2017/results/res2022q1/cpu2017-20...  \n27477  <A HREF=\"/cpu2017/results/res2022q1/cpu2017-20...  \n\n[27478 rows x 14 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Company</th>\n      <th>System</th>\n      <th>Result</th>\n      <th># cores</th>\n      <th># chips</th>\n      <th>Processor</th>\n      <th>CPU Speed(MHz)</th>\n      <th>1st Cache per core(KB)</th>\n      <th>2nd Cache per core(KB)</th>\n      <th>3rd Cache per chip(MB)</th>\n      <th>Other Cache per chip(MB)</th>\n      <th>Memory(GB)</th>\n      <th>Updated</th>\n      <th>Report Link</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUS ESC4000A-E10(KRPG-U8) Server System 2.60 ...</td>\n      <td>8.87</td>\n      <td>64</td>\n      <td>1</td>\n      <td>AMD EPYC 7H12</td>\n      <td>2600</td>\n      <td>32 KB I + 32 KB D on chip per core</td>\n      <td>512 KB I+D on chip per core</td>\n      <td>256 MB I+D on chip per chip, 16 MB shared / 4 ...</td>\n      <td>None</td>\n      <td>512 GB (8 x 64 GB 2Rx4 PC4-3200AA-R)</td>\n      <td>Jul-2020</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2020q3/cpu2017-20...</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...</td>\n      <td>5.05</td>\n      <td>12</td>\n      <td>2</td>\n      <td>Intel Xeon Bronze 3204</td>\n      <td>1900</td>\n      <td>32 KB I + 32 KB D on chip per core</td>\n      <td>1 MB I+D on chip per core</td>\n      <td>8.25 MB I+D on chip per chip</td>\n      <td>None</td>\n      <td>768 GB (24 x 32 GB 2Rx4 PC4-2933Y-R, running a...</td>\n      <td>Dec-2020</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2019q3/cpu2017-20...</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...</td>\n      <td>5.15</td>\n      <td>16</td>\n      <td>2</td>\n      <td>Intel Xeon Bronze 3206R</td>\n      <td>1900</td>\n      <td>32 KB I + 32 KB D on chip per core</td>\n      <td>1 MB I+D on chip per core</td>\n      <td>11 MB I+D on chip per chip</td>\n      <td>None</td>\n      <td>768 GB (24 x 32 GB 2Rx4 PC4-2933Y-R, running a...</td>\n      <td>Jul-2020</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2020q3/cpu2017-20...</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...</td>\n      <td>10.00</td>\n      <td>44</td>\n      <td>2</td>\n      <td>Intel Xeon Gold 6238T</td>\n      <td>1900</td>\n      <td>32 KB I + 32 KB D on chip per core</td>\n      <td>1 MB I+D on chip per core</td>\n      <td>30.25 MB I+D on chip per chip</td>\n      <td>None</td>\n      <td>768 GB (24 x 32 GB 2Rx4 PC4-2933Y-R)</td>\n      <td>Dec-2020</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2019q2/cpu2017-20...</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUS ESC8000 G4(Z11PG-D24) Server System (2.10...</td>\n      <td>11.70</td>\n      <td>40</td>\n      <td>2</td>\n      <td>Intel Xeon Gold 5218R</td>\n      <td>2100</td>\n      <td>32 KB I + 32 KB D on chip per core</td>\n      <td>1 MB I+D on chip per core</td>\n      <td>27.5 MB I+D on chip per chip</td>\n      <td>None</td>\n      <td>768 GB (24 x 32 GB 2Rx4 PC4-2933Y-R, running a...</td>\n      <td>Aug-2020</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2020q3/cpu2017-20...</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>27473</th>\n      <td>xFusion</td>\n      <td>xFusion 5288 V6 (Intel Xeon Silver 4310)</td>\n      <td>203.00</td>\n      <td>24</td>\n      <td>2</td>\n      <td>Intel Xeon Silver 4310</td>\n      <td>2100</td>\n      <td>32 KB I + 48 KB D on chip per core</td>\n      <td>1.25 MB I+D on chip per core</td>\n      <td>18 MB I+D on chip per chip</td>\n      <td>None</td>\n      <td>512 GB (16 x 32 GB 2Rx4 PC4-3200AA-R, running ...</td>\n      <td>Apr-2022</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...</td>\n    </tr>\n    <tr>\n      <th>27474</th>\n      <td>xFusion</td>\n      <td>xFusion 5288 V6 (Intel Xeon Silver 4314)</td>\n      <td>264.00</td>\n      <td>32</td>\n      <td>2</td>\n      <td>Intel Xeon Silver 4314</td>\n      <td>2400</td>\n      <td>32 KB I + 48 KB D on chip per core</td>\n      <td>1.25 MB I+D on chip per core</td>\n      <td>24 MB I+D on chip per chip</td>\n      <td>None</td>\n      <td>512 GB (16 x 32 GB 2Rx4 PC4-3200AA-R, running ...</td>\n      <td>Apr-2022</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...</td>\n    </tr>\n    <tr>\n      <th>27475</th>\n      <td>xFusion</td>\n      <td>xFusion 5288 V6 (Intel Xeon Silver 4316)</td>\n      <td>300.00</td>\n      <td>40</td>\n      <td>2</td>\n      <td>Intel Xeon Silver 4316</td>\n      <td>2300</td>\n      <td>32 KB I + 48 KB D on chip per core</td>\n      <td>1.25 MB I+D on chip per core</td>\n      <td>30 MB I+D on chip per chip</td>\n      <td>None</td>\n      <td>512 GB (16 x 32 GB 2Rx4 PC4-3200AA-R, running ...</td>\n      <td>Apr-2022</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2022q2/cpu2017-20...</td>\n    </tr>\n    <tr>\n      <th>27476</th>\n      <td>xFusion</td>\n      <td>xFusion KunLun 9008 V5 (Intel Xeon Platinum 8280)</td>\n      <td>1150.00</td>\n      <td>224</td>\n      <td>8</td>\n      <td>Intel Xeon Platinum 8280</td>\n      <td>2700</td>\n      <td>32 KB I + 32 KB D on chip per core</td>\n      <td>1 MB I+D on chip per core</td>\n      <td>38.5 MB I+D on chip per chip</td>\n      <td>None</td>\n      <td>3 TB (96 x 32 GB 2Rx4 PC4-2933Y-R)</td>\n      <td>Mar-2022</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2022q1/cpu2017-20...</td>\n    </tr>\n    <tr>\n      <th>27477</th>\n      <td>xFusion</td>\n      <td>xFusion KunLun 9008 V5 (Intel Xeon Platinum 8280)</td>\n      <td>1140.00</td>\n      <td>224</td>\n      <td>8</td>\n      <td>Intel Xeon Platinum 8280</td>\n      <td>2700</td>\n      <td>32 KB I + 32 KB D on chip per core</td>\n      <td>1 MB I+D on chip per core</td>\n      <td>38.5 MB I+D on chip per chip</td>\n      <td>None</td>\n      <td>3 TB (96 x 32 GB 2Rx4 PC4-2933Y-R)</td>\n      <td>Mar-2022</td>\n      <td>&lt;A HREF=\"/cpu2017/results/res2022q1/cpu2017-20...</td>\n    </tr>\n  </tbody>\n</table>\n<p>27478 rows × 14 columns</p>\n</div>"
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# cpu2017数据源，感兴趣的属性\n",
    "ods_cpu2017_col = [\"Hardware Vendor\t\", \"System\", \"Base Result\", \"# Cores\", \"# Chips \", \"Processor \", \"Processor MHz\",\n",
    "                   \"1st Level Cache\", \"2nd Level Cache\", \"3rd Level Cache\", \"Other Cache\", \"Memory\", \"Updated \",\n",
    "                   \"Disclosure\"]\n",
    "\n",
    "# 抽取相关属性，构成DataFrame，前缀为ods表明为贴源数据层（其中# chips为中间数据，数据清洗完成后应当删去）\n",
    "ods_cpu2017_data = pd.read_csv(get_latest_results(benchmark_name=\"cpu2017\"), usecols=ods_cpu2017_col)\n",
    "\n",
    "# 更新部分属性名 [\"Company\", \"System\", \"Result\", \"# cores\", \"Processor\", \"CPU Speed(MHz)\", \"1st Cache per core(KB)\",\n",
    "# \"2nd Cache per core(KB)\", \"3rd Cache per chip(MB)\", \"Other Cache per chip(MB)\", \"Memory(GB)\", \"Updated\",\n",
    "# \"Report Link\"]\n",
    "ods_cpu2017_data = ods_cpu2017_data.rename(columns={\"Hardware Vendor\t\": \"Company\"})\n",
    "ods_cpu2017_data = ods_cpu2017_data.rename(columns={\"Base Result\": \"Result\"})\n",
    "ods_cpu2017_data = ods_cpu2017_data.rename(columns={\"# Cores\": \"# cores\"})\n",
    "ods_cpu2017_data = ods_cpu2017_data.rename(columns={\"# Chips \": \"# chips\"})\n",
    "ods_cpu2017_data = ods_cpu2017_data.rename(columns={\"Processor \": \"Processor\"})\n",
    "ods_cpu2017_data = ods_cpu2017_data.rename(columns={\"Processor MHz\": \"CPU Speed(MHz)\"})\n",
    "ods_cpu2017_data = ods_cpu2017_data.rename(columns={\"1st Level Cache\": \"1st Cache per core(KB)\"})\n",
    "ods_cpu2017_data = ods_cpu2017_data.rename(columns={\"2nd Level Cache\": \"2nd Cache per core(KB)\"})\n",
    "ods_cpu2017_data = ods_cpu2017_data.rename(columns={\"3rd Level Cache\": \"3rd Cache per chip(MB)\"})\n",
    "ods_cpu2017_data = ods_cpu2017_data.rename(columns={\"Other Cache\": \"Other Cache per chip(MB)\"})\n",
    "ods_cpu2017_data = ods_cpu2017_data.rename(columns={\"Memory\": \"Memory(GB)\"})\n",
    "ods_cpu2017_data = ods_cpu2017_data.rename(columns={\"Updated \": \"Updated\"})\n",
    "ods_cpu2017_data = ods_cpu2017_data.rename(columns={\"Disclosure\": \"Report Link\"})\n",
    "\n",
    "ods_cpu2017_data"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 4.2 数据转换和清洗\n",
    "\n",
    "引入需要的库，后续对每一个属性的值，写出一个转换函数，即原始值X到转换后的值Y的函数，这个函数可以套用到DataFrame的map方法或者apply方法，用于批量操作"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "outputs": [],
   "source": [
    "import re\n",
    "from typing import Union"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "\n",
    "#### 1. Company属性\n",
    "\n",
    "1. 例如“Sun Microsystems”和“Sun Microsystems, Inc.”，应当统一为不含逗号的\n",
    "2. 对于“GIGA-BYTE TECHNOLOGY CO. LTD”和“GIGA-BYTE TECHNOLOGY CO. LTD.”，遵循少数服从多数的原则将“GIGA-BYTE TECHNOLOGY CO. LTD”修改为“GIGA-BYTE TECHNOLOGY CO. LTD.”\n",
    "3. 其余的缩写，例如“Inc.”则不加以处理"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "outputs": [],
   "source": [
    "def company_rule(x: str) -> str:\n",
    "    \"\"\"\n",
    "    对Company这一列数据的处理操作，用于DataFrame的map方法\n",
    "    :param x: Company属性的值\n",
    "    :return: 处理后的值\n",
    "    \"\"\"\n",
    "    y = x.replace(\",\", \"\")    # 去除逗号\n",
    "    y = re.sub(r\"GIGA-BYTE TECHNOLOGY CO. LTD$\", \"GIGA-BYTE TECHNOLOGY CO. LTD.\", y)    # 正则表达式替换：\\b匹配单词边界，$匹配字符串末尾\n",
    "\n",
    "    return y"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "\n",
    "#### 2. System属性：不处理\n",
    "\n",
    "#### 3. Result属性：不处理\n",
    "\n",
    "#### 4. \\# cores属性：不处理\n",
    "\n",
    "#### 5. Processor属性：未处理\n",
    "\n",
    "应当向cpu2017数据源的Processor属性的取值看齐"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 6. CPU Speed属性：不处理"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 7. Cache相关属性\n",
    "\n",
    "1. 1st Cache属性，单位为KB，统一为per core，有2种情况：\n",
    "    1. \\[case 1\\] 分icache和dcache（例如”32KB(I)+32KB(D) on chip, per core“），那么应当整理为”XXKB(I)+XXKB(D)“，这里都是per core的大小（如果计算总大小，应当乘以core数量）\n",
    "    2. \\[case 2\\] “redacted”，返回“0”\n",
    "2. 2nd Cache属性，单位为KB，统一为per core的大小，有多种情况：\n",
    "    1. \\[case\\] \".*I+D.*\"\n",
    "       1. \\[case\\] “per core”\n",
    "            - \\[case1\\] “KB” eg. 256 KB I+D on chip per core\n",
    "                利用正则匹配，取出数字，整理为“XXKB(I+D)”\n",
    "            - \\[case2\\] \"MB\" eg. 1 MB I+D on chip per core\n",
    "                利用正则匹配，取出数字，乘以1024，转化为KB，整理为“XXKB(I+D)”\n",
    "       2. \\[case\\] “per chip”\n",
    "            - \\[case3\\] \"MB\" eg. 2 MB I+D on chip per chip\n",
    "                利用正则匹配，取出数字，乘以1024，借助“# chips”和“# cores”两个属性值，转换成per core，整理为“XXKB(I+D)”\n",
    "    2. \\[case\\] \".*I.*;.*D.*\"\n",
    "        - \\[case4\\] “per chip” “MB” eg. 2 MB I on chip per chip (256 KB / 4 cores); 4 MB D on chip per chip (256 KB / 2 cores)\n",
    "            利用正则匹配，取出数字，乘以1024，借助“# chips”和“# cores”两个属性值，转换成per core，整理为“XXKB(I)+XXKB(D)”\n",
    "    3. \\[case\\] \"redacted\"\n",
    "        - \\[case5\\] \"redacted\"\n",
    "            返回\"0\"\n",
    "3. 3rd Cache属性, 单位为MB，统一为per chip的大小，有多种情况：\n",
    "    1. \\[case\\] \".*I+D.*\"\n",
    "        1. \\[case\\] 含“per core” “MB”\n",
    "            - \\[case1\\] “on chip per core” eg. 8 MB I+D on chip per core\n",
    "                利用正则匹配，取出数字，借助“# chips”和“# cores”两个属性值，转换成per chip，整理为“XXMB”\n",
    "            - \\[case2\\] “on chip per chip” eg. 128 MB I+D on chip per chip, 16 MB per core\n",
    "                利用正则匹配，取出数字，整理为“XXMB”\n",
    "        2. \\[case\\] “per chip” “MB”\n",
    "            - \\[case3\\] eg. 256 MB I+D on chip per chip, 16 MB shared / 4 cores\n",
    "                利用正则匹配，取出数字，整理为“XXMB”\n",
    "    2. \\[case\\] \"redacted\"\n",
    "        - \\[case4\\] \"redacted\"\n",
    "            返回\"0\"\n",
    "4. Other Cache属性\n",
    "    - \\[case1\\] “None”\n",
    "        返回 0\n",
    "    - \\[case2\\] eg. 16 MB I+D off chip per 8 DIMMs\n",
    "        返回原值"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "outputs": [],
   "source": [
    "# 4.2.7 Cache\n",
    "# 4.2.7.1 1st Cache\n",
    "def first_cache_rule(x: str) -> str:\n",
    "    \"\"\"\n",
    "    对1st Cache这一列数据的处理操作，用于DataFrame的map方法\n",
    "    :param x: 1st Cache属性的值\n",
    "    :return: 处理后的1st Cache per core(KB)值\n",
    "    \"\"\"\n",
    "    if x.find(\"per core\") != -1:\n",
    "        # 说明：(group1: 1个以上数字)+可能有空格+'KB'+可能有空格+I'+可能有空格+'+'+可能有空格+(group2: 1个以上数字)+可能有空格+'KB'+可能有空格+'D'\n",
    "        search_obj = re.match(r\"(\\d+)\\s*KB\\s*I\\s*\\+\\s*(\\d+)\\s*KB\\s*D\", x)\n",
    "        l1i_kb = search_obj.group(1)\n",
    "        l1d_kb = search_obj.group(2)\n",
    "        return f\"{l1i_kb}KB(I)+{l1d_kb}KB(D)\"\n",
    "    elif x.find(\"redacted\") != -1:\n",
    "        return \"0\"\n",
    "    else:\n",
    "        # 说明：(group1: 1个以上数字)+可能有空格+'KB'\n",
    "        search_obj = re.match(r\"(\\d+)\\s*KB\", x)\n",
    "        l1_kb = search_obj.group(1)\n",
    "        return f\"{l1_kb}KB(I+D)\"\n",
    "\n",
    "\n",
    "# 4.2.7.2 2nd Cache\n",
    "def second_cache_rule(line: pd.Series) -> str:\n",
    "    \"\"\"\n",
    "    对2nd Cache这一列数据的处理操作，用于DataFrame的apply方法（需要用到其他属性的值）\n",
    "    :param line: DataFrame的一行，类型是pd.Series\n",
    "    :return: 处理后的2nd Cache per core(KB)值\n",
    "    \"\"\"\n",
    "    x = line[\"2nd Cache per core(KB)\"]\n",
    "    cores_num = line[\"# cores\"]\n",
    "    chips_num = line[\"# chips\"]\n",
    "    if x.find(\"I+D\") != -1:\n",
    "        if x.find(\"per core\") != -1:\n",
    "            if x.find(\"KB\") != -1:  #eg. 256 KB I+D on chip per core\n",
    "                search_obj = re.match(r\"(\\d+)\\s*KB\", x)\n",
    "                l2_kb = search_obj.group(1)\n",
    "                return f\"{l2_kb}KB(I+D)\"\n",
    "            else:   # eg. 1 MB I+D on chip per core\n",
    "                search_obj = re.match(r\"(\\d+(\\.\\d+)?)\\s*MB\", x)\n",
    "                l2_kb = int(float(search_obj.group(1)) * 1024)\n",
    "                return f\"{l2_kb}KB(I+D)\"\n",
    "        elif x.find(\"per chip\") != -1:    # eg. 2 MB I+D on chip per chip\n",
    "            search_obj = re.match(r\"(\\d+)\\s*MB\", x)\n",
    "            l2_kb = int(int(search_obj.group(1)) * 1024 * chips_num / float(cores_num))\n",
    "            return f\"{l2_kb}KB(I+D)\"\n",
    "    else:\n",
    "        if x.find(\"redacted\") != -1:\n",
    "            return \"0\"\n",
    "        else:\n",
    "            # 说明：(group1: 1个以上数字)+可能有空格+'KB'+可能有空格+I'+可能有空格+'+'+可能有空格+(group2: 1个以上数字)+可能有空格+'KB'+可能有空格+'D'\n",
    "            # eg. 2 MB I on chip per chip (256 KB / 4 cores); 4 MB D on chip per chip (256 KB / 2 cores)\n",
    "            search_obj = re.match(r\"(\\d+)\\s*MB\\s*I.*(\\d+)\\s*MB\\s*D\", x)\n",
    "            l1i_kb = int(int(search_obj.group(1)) * 1024 * chips_num / float(cores_num))\n",
    "            l1d_kb = int(int(search_obj.group(2)) * 1024 * chips_num / float(cores_num))\n",
    "            return f\"{l1i_kb}KB(I)+{l1d_kb}KB(D)\"\n",
    "\n",
    "\n",
    "# 4.2.7.3 3rd Cache\n",
    "def third_cache_rule(line: pd.Series) -> str:\n",
    "    \"\"\"\n",
    "    对3rd Cache这一列数据的处理操作，用于DataFrame的apply方法（需要用到其他属性的值）\n",
    "    :param line: DataFrame的一行，类型是pd.Series\n",
    "    :return: 处理后的3rd Cache per chip(MB)值\n",
    "    \"\"\"\n",
    "    x = line[\"3rd Cache per chip(MB)\"]\n",
    "    cores_num = line[\"# cores\"]\n",
    "    chips_num = line[\"# chips\"]\n",
    "    if x.find(\"I+D\") != -1:\n",
    "        if x.find(\"per core\") != -1:\n",
    "            if x.find(\"on chip per core\") != -1:  # eg. 8 MB I+D on chip per core\n",
    "                search_obj = re.match(r\"(\\d+)\\s*MB\", x)\n",
    "                l2_mb = int(int(search_obj.group(1)) * cores_num / float(chips_num))\n",
    "                return f\"{l2_mb}MB\"\n",
    "            elif x.find(\"on chip per chip\") != -1:  # eg. 128 MB I+D on chip per chip, 16 MB per core\n",
    "                search_obj = re.match(r\"(\\d+(\\.\\d+)?)\\s*MB\\s*I\\+D\", x)\n",
    "                l2_mb = search_obj.group(1)\n",
    "                return f\"{l2_mb}MB\"\n",
    "        else: # eg. 256 MB I+D on chip per chip, 16 MB shared / 4 cores\n",
    "            search_obj = re.match(r\"(\\d+(\\.\\d+)?)\\s*MB\\s*I\\+D\", x)\n",
    "            l2_mb = search_obj.group(1)\n",
    "            return f\"{l2_mb}MB\"\n",
    "    else:\n",
    "        if x.find(\"redacted\") != -1:\n",
    "            return \"0\"\n",
    "        else:\n",
    "            # 说明：(group1: 1个以上数字)+可能有空格+'MB'\n",
    "            search_obj = re.match(r\"(\\d+)\\s*MB\", x)\n",
    "            l1_mb = search_obj.group(1)\n",
    "            return f\"{l1_mb}MB\"\n",
    "\n",
    "\n",
    "# 4.2.7.4 other Cache\n",
    "def other_cache_rule(x: str) -> str:\n",
    "    \"\"\"\n",
    "    对other Cache这一列数据的处理操作，用于DataFrame的map方法\n",
    "    :param x: other Cache属性的值\n",
    "    :return: 处理后的other Cache per chip(MB)值\n",
    "    \"\"\"\n",
    "    if x.find(\"None\") != -1:\n",
    "        return \"0\"\n",
    "    else:   # eg. 16 MB I+D off chip per 8 DIMMs\n",
    "        search_obj = re.match(r\"(\\d+(\\.\\d+)?)\\s*MB\\s*I\\+D\", x)\n",
    "        l2_mb = search_obj.group(1)\n",
    "        return f\"{l2_mb}MB\""
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 8. Memory属性\n",
    "\n",
    "数据单位统一为“GB”，即“XXGB”的格式，将其中单位为“MB”的化为“GB”，单位为“TB”的化为“GB”，部分没有单位的，其单位视为“MB”。例如“4096MB”应当化为“4GB”，例如“262144”应当化为“256GB”"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "outputs": [],
   "source": [
    "# 4.2.8 Memory\n",
    "def memory_rule(x: str) -> str:\n",
    "    \"\"\"\n",
    "    对Memory这一列数据的处理操作，用于DataFrame的map方法\n",
    "    :param x: Memory属性的值\n",
    "    :return: 处理后的Memory(GB)值\n",
    "    \"\"\"\n",
    "    y = re.sub(r\"\\(.*\\)\", \"\", x)  # 删除括号中的内容\n",
    "    y = y.rstrip()  # 删除末尾的空格\n",
    "    if y.find(\"MB\") != -1:\n",
    "        search_obj = re.match(r\"(\\d+)\\s*MB\", y)\n",
    "        memory_gb = int(int(search_obj.group(1)) / 1024.0)\n",
    "        return f\"{memory_gb}GB\"\n",
    "    elif y.find(\"GB\") != -1:\n",
    "        search_obj = re.match(r\"(\\d+)\\s*GB\", y)\n",
    "        return f\"{search_obj.group(1)}GB\"\n",
    "    elif y.find(\"TB\") != -1:\n",
    "        search_obj = re.match(r\"(\\d+)\\s*TB\", y)\n",
    "        memory_gb = int(int(search_obj.group(1)) * 1024)\n",
    "        return f\"{memory_gb}GB\"\n",
    "    else:\n",
    "        return f\"{int(int(y) / 1024.0)}GB\"\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 9. Updated属性：不处理\n",
    "\n",
    "#### 10. Disclosure属性\n",
    "\n",
    "给出了HTML和Text格式的报告链接，其中链接指向HTML的详细信息页面，将对应超链接的地址和“https://www.spec.org”拼接，得到对应完整链接，即\n",
    "\n",
    "\"<A HREF=\"\"/jvm2008/results/res2008q3/jvm2008-20080617-00001.html\"\">HTML</A> <A HREF=\"\"/jvm2008/results/res2008q3/jvm2008-20080617-00001.txt\"\">Text</A>\""
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "outputs": [],
   "source": [
    "def report_link_rule(x: str) -> str:\n",
    "    \"\"\"\n",
    "    对Disclosure这一列数据的处理操作，用于DataFrame的map方法\n",
    "    :param x: Disclosure属性的值\n",
    "    :return: 处理后的Report Link值\n",
    "    \"\"\"\n",
    "    search_obj = re.match(r'<A HREF=\"(.*)\">HTML</A>',x)\n",
    "    return f\"https://www.spec.org{search_obj.group(1)}\""
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "逐一应用上述方法"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "outputs": [
    {
     "data": {
      "text/plain": "                     Company  \\\n0      ASUSTeK Computer Inc.   \n1      ASUSTeK Computer Inc.   \n2      ASUSTeK Computer Inc.   \n3      ASUSTeK Computer Inc.   \n4      ASUSTeK Computer Inc.   \n...                      ...   \n27473                xFusion   \n27474                xFusion   \n27475                xFusion   \n27476                xFusion   \n27477                xFusion   \n\n                                                  System   Result  # cores  \\\n0      ASUS ESC4000A-E10(KRPG-U8) Server System 2.60 ...     8.87       64   \n1      ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...     5.05       12   \n2      ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...     5.15       16   \n3      ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...    10.00       44   \n4      ASUS ESC8000 G4(Z11PG-D24) Server System (2.10...    11.70       40   \n...                                                  ...      ...      ...   \n27473           xFusion 5288 V6 (Intel Xeon Silver 4310)   203.00       24   \n27474           xFusion 5288 V6 (Intel Xeon Silver 4314)   264.00       32   \n27475           xFusion 5288 V6 (Intel Xeon Silver 4316)   300.00       40   \n27476  xFusion KunLun 9008 V5 (Intel Xeon Platinum 8280)  1150.00      224   \n27477  xFusion KunLun 9008 V5 (Intel Xeon Platinum 8280)  1140.00      224   \n\n                      Processor  CPU Speed(MHz) 1st Cache per core(KB)  \\\n0                 AMD EPYC 7H12            2600        32KB(I)+32KB(D)   \n1        Intel Xeon Bronze 3204            1900        32KB(I)+32KB(D)   \n2       Intel Xeon Bronze 3206R            1900        32KB(I)+32KB(D)   \n3         Intel Xeon Gold 6238T            1900        32KB(I)+32KB(D)   \n4         Intel Xeon Gold 5218R            2100        32KB(I)+32KB(D)   \n...                         ...             ...                    ...   \n27473    Intel Xeon Silver 4310            2100        32KB(I)+48KB(D)   \n27474    Intel Xeon Silver 4314            2400        32KB(I)+48KB(D)   \n27475    Intel Xeon Silver 4316            2300        32KB(I)+48KB(D)   \n27476  Intel Xeon Platinum 8280            2700        32KB(I)+32KB(D)   \n27477  Intel Xeon Platinum 8280            2700        32KB(I)+32KB(D)   \n\n      2nd Cache per core(KB) 3rd Cache per chip(MB) Other Cache per chip(MB)  \\\n0                 512KB(I+D)                  256MB                        0   \n1                1024KB(I+D)                 8.25MB                        0   \n2                1024KB(I+D)                   11MB                        0   \n3                1024KB(I+D)                30.25MB                        0   \n4                1024KB(I+D)                 27.5MB                        0   \n...                      ...                    ...                      ...   \n27473            1280KB(I+D)                   18MB                        0   \n27474            1280KB(I+D)                   24MB                        0   \n27475            1280KB(I+D)                   30MB                        0   \n27476            1024KB(I+D)                 38.5MB                        0   \n27477            1024KB(I+D)                 38.5MB                        0   \n\n      Memory(GB)   Updated                                        Report Link  \n0          512GB  Jul-2020  https://www.spec.org/cpu2017/results/res2020q3...  \n1          768GB  Dec-2020  https://www.spec.org/cpu2017/results/res2019q3...  \n2          768GB  Jul-2020  https://www.spec.org/cpu2017/results/res2020q3...  \n3          768GB  Dec-2020  https://www.spec.org/cpu2017/results/res2019q2...  \n4          768GB  Aug-2020  https://www.spec.org/cpu2017/results/res2020q3...  \n...          ...       ...                                                ...  \n27473      512GB  Apr-2022  https://www.spec.org/cpu2017/results/res2022q2...  \n27474      512GB  Apr-2022  https://www.spec.org/cpu2017/results/res2022q2...  \n27475      512GB  Apr-2022  https://www.spec.org/cpu2017/results/res2022q2...  \n27476     3072GB  Mar-2022  https://www.spec.org/cpu2017/results/res2022q1...  \n27477     3072GB  Mar-2022  https://www.spec.org/cpu2017/results/res2022q1...  \n\n[27478 rows x 13 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Company</th>\n      <th>System</th>\n      <th>Result</th>\n      <th># cores</th>\n      <th>Processor</th>\n      <th>CPU Speed(MHz)</th>\n      <th>1st Cache per core(KB)</th>\n      <th>2nd Cache per core(KB)</th>\n      <th>3rd Cache per chip(MB)</th>\n      <th>Other Cache per chip(MB)</th>\n      <th>Memory(GB)</th>\n      <th>Updated</th>\n      <th>Report Link</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUS ESC4000A-E10(KRPG-U8) Server System 2.60 ...</td>\n      <td>8.87</td>\n      <td>64</td>\n      <td>AMD EPYC 7H12</td>\n      <td>2600</td>\n      <td>32KB(I)+32KB(D)</td>\n      <td>512KB(I+D)</td>\n      <td>256MB</td>\n      <td>0</td>\n      <td>512GB</td>\n      <td>Jul-2020</td>\n      <td>https://www.spec.org/cpu2017/results/res2020q3...</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...</td>\n      <td>5.05</td>\n      <td>12</td>\n      <td>Intel Xeon Bronze 3204</td>\n      <td>1900</td>\n      <td>32KB(I)+32KB(D)</td>\n      <td>1024KB(I+D)</td>\n      <td>8.25MB</td>\n      <td>0</td>\n      <td>768GB</td>\n      <td>Dec-2020</td>\n      <td>https://www.spec.org/cpu2017/results/res2019q3...</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...</td>\n      <td>5.15</td>\n      <td>16</td>\n      <td>Intel Xeon Bronze 3206R</td>\n      <td>1900</td>\n      <td>32KB(I)+32KB(D)</td>\n      <td>1024KB(I+D)</td>\n      <td>11MB</td>\n      <td>0</td>\n      <td>768GB</td>\n      <td>Jul-2020</td>\n      <td>https://www.spec.org/cpu2017/results/res2020q3...</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUS ESC8000 G4(Z11PG-D24) Server System (1.90...</td>\n      <td>10.00</td>\n      <td>44</td>\n      <td>Intel Xeon Gold 6238T</td>\n      <td>1900</td>\n      <td>32KB(I)+32KB(D)</td>\n      <td>1024KB(I+D)</td>\n      <td>30.25MB</td>\n      <td>0</td>\n      <td>768GB</td>\n      <td>Dec-2020</td>\n      <td>https://www.spec.org/cpu2017/results/res2019q2...</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>ASUSTeK Computer Inc.</td>\n      <td>ASUS ESC8000 G4(Z11PG-D24) Server System (2.10...</td>\n      <td>11.70</td>\n      <td>40</td>\n      <td>Intel Xeon Gold 5218R</td>\n      <td>2100</td>\n      <td>32KB(I)+32KB(D)</td>\n      <td>1024KB(I+D)</td>\n      <td>27.5MB</td>\n      <td>0</td>\n      <td>768GB</td>\n      <td>Aug-2020</td>\n      <td>https://www.spec.org/cpu2017/results/res2020q3...</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>27473</th>\n      <td>xFusion</td>\n      <td>xFusion 5288 V6 (Intel Xeon Silver 4310)</td>\n      <td>203.00</td>\n      <td>24</td>\n      <td>Intel Xeon Silver 4310</td>\n      <td>2100</td>\n      <td>32KB(I)+48KB(D)</td>\n      <td>1280KB(I+D)</td>\n      <td>18MB</td>\n      <td>0</td>\n      <td>512GB</td>\n      <td>Apr-2022</td>\n      <td>https://www.spec.org/cpu2017/results/res2022q2...</td>\n    </tr>\n    <tr>\n      <th>27474</th>\n      <td>xFusion</td>\n      <td>xFusion 5288 V6 (Intel Xeon Silver 4314)</td>\n      <td>264.00</td>\n      <td>32</td>\n      <td>Intel Xeon Silver 4314</td>\n      <td>2400</td>\n      <td>32KB(I)+48KB(D)</td>\n      <td>1280KB(I+D)</td>\n      <td>24MB</td>\n      <td>0</td>\n      <td>512GB</td>\n      <td>Apr-2022</td>\n      <td>https://www.spec.org/cpu2017/results/res2022q2...</td>\n    </tr>\n    <tr>\n      <th>27475</th>\n      <td>xFusion</td>\n      <td>xFusion 5288 V6 (Intel Xeon Silver 4316)</td>\n      <td>300.00</td>\n      <td>40</td>\n      <td>Intel Xeon Silver 4316</td>\n      <td>2300</td>\n      <td>32KB(I)+48KB(D)</td>\n      <td>1280KB(I+D)</td>\n      <td>30MB</td>\n      <td>0</td>\n      <td>512GB</td>\n      <td>Apr-2022</td>\n      <td>https://www.spec.org/cpu2017/results/res2022q2...</td>\n    </tr>\n    <tr>\n      <th>27476</th>\n      <td>xFusion</td>\n      <td>xFusion KunLun 9008 V5 (Intel Xeon Platinum 8280)</td>\n      <td>1150.00</td>\n      <td>224</td>\n      <td>Intel Xeon Platinum 8280</td>\n      <td>2700</td>\n      <td>32KB(I)+32KB(D)</td>\n      <td>1024KB(I+D)</td>\n      <td>38.5MB</td>\n      <td>0</td>\n      <td>3072GB</td>\n      <td>Mar-2022</td>\n      <td>https://www.spec.org/cpu2017/results/res2022q1...</td>\n    </tr>\n    <tr>\n      <th>27477</th>\n      <td>xFusion</td>\n      <td>xFusion KunLun 9008 V5 (Intel Xeon Platinum 8280)</td>\n      <td>1140.00</td>\n      <td>224</td>\n      <td>Intel Xeon Platinum 8280</td>\n      <td>2700</td>\n      <td>32KB(I)+32KB(D)</td>\n      <td>1024KB(I+D)</td>\n      <td>38.5MB</td>\n      <td>0</td>\n      <td>3072GB</td>\n      <td>Mar-2022</td>\n      <td>https://www.spec.org/cpu2017/results/res2022q1...</td>\n    </tr>\n  </tbody>\n</table>\n<p>27478 rows × 13 columns</p>\n</div>"
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ods_cpu2017_data[\"Company\"] = ods_cpu2017_data[\"Company\"].map(company_rule)\n",
    "# ods_cpu2017_data[\"Processor\"] = ods_cpu2017_data[\"Processor\"].map(processor_rule)\n",
    "# ods_cpu2017_data[\"CPU Speed(MHz)\"] = ods_cpu2017_data[\"CPU Speed(MHz)\"].map(cpu_speed_rule)\n",
    "ods_cpu2017_data[\"1st Cache per core(KB)\"] = ods_cpu2017_data[\"1st Cache per core(KB)\"].map(first_cache_rule)\n",
    "ods_cpu2017_data[\"2nd Cache per core(KB)\"] = ods_cpu2017_data.apply(second_cache_rule, axis=1)\n",
    "ods_cpu2017_data[\"3rd Cache per chip(MB)\"] = ods_cpu2017_data.apply(third_cache_rule, axis=1)\n",
    "ods_cpu2017_data[\"Other Cache per chip(MB)\"] = ods_cpu2017_data[\"Other Cache per chip(MB)\"].map(other_cache_rule)\n",
    "ods_cpu2017_data[\"Memory(GB)\"] = ods_cpu2017_data[\"Memory(GB)\"].map(memory_rule)\n",
    "ods_cpu2017_data[\"Report Link\"] = ods_cpu2017_data[\"Report Link\"].map(report_link_rule)\n",
    "# ods_cpu2017_data = ods_cpu2017_data.drop(columns=[\"# cores per chip\", \"CPU Speed\", \"1st Cache\", \"2nd Cache\", \"Other Cache\", \"Memory\", \"Disclosure\"])\n",
    "\n",
    "ods_order = [\"Company\", \"System\", \"Result\", \"# cores\", \"Processor\", \"CPU Speed(MHz)\", \"1st Cache per core(KB)\",\n",
    "             \"2nd Cache per core(KB)\", \"3rd Cache per chip(MB)\", \"Other Cache per chip(MB)\", \"Memory(GB)\",\n",
    "             \"Updated\", \"Report Link\"]\n",
    "\n",
    "ods_cpu2017_data = ods_cpu2017_data[ods_order]\n",
    "ods_cpu2017_data.to_csv(r'ods_cpu2017_data.csv')\n",
    "ods_cpu2017_data\n",
    "# print(ods_cpu2017_data)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "数据清洗没有问题，但是需要把这个数据源拆成4张表格\n",
    "cpu2017_intspeed, cpu2017_intrate, cpu2017_fpspeed, cpu2017_fprate"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}